<?php

use Guzzle\Plugin\Backoff\BackoffLogger;
class PurchaseOrderController extends BaseController {
	
	// Table
	public $table = 'purchase_order';
	
	// Eloquent Model
	public $model = 'PurchaseOrder';
	
	// Base
	public $base = 'po';
	
	// Localization
	public $local = 'en';
	
	// Language Name
	public $language = 'po';
	
	// Breadcrumbs
	public $breadcrumbs = [ ['link' => 'po' , 'title' => 'Purchase Order'] ];

	// Set List Data - Input, Object
	public function setListData( $data, $object ) {
		$object = $this->_filterPoList( $data, $object );
		return $object;
	}

	/**
	 * Filter PO List from Form Filters.
	 * @author AngelaMisa 03/03/2015 11:44PM
	 * @param $data, $object
	 * @return $mixed
	 */
	private function _filterPoList( $data, $object ) {
		if ( !empty($data['filter_id']) )
			$object = $object->where( 'id', (int) $data['filter_id'] );
		
		if ( !empty($data['filter_po_number']) ) {
			$po_num_list = explode('-', trim($data['filter_po_number']));
		
			if ( count($po_num_list) == 3 ) {
				$id = ltrim(trim($po_num_list[1]), 1);
				$vendor = trim($po_num_list[0]);
				$po_date = date_create_from_format( 'Ymd', trim($po_num_list[2]) );
		
				$object = $object->where( 'id', $id )
				->whereHas('vendor', function( $q ) use ( $vendor) {
					$q->where( 'code', $vendor );
				})
				->where( DB::RAW("DATE_FORMAT(created_at, '%Y-%m-%d')"), $po_date->format('Y-m-d') );
			}
			// PO Number doesn't exist
			else
				$object = $object->where( 'id', 0 );
		}
		if ( !empty($data['filter_vendor']) )
			$object = $object->where('VENDOR_id', $data['filter_vendor'] );
		
		if ( isset($data['filter_status']) && $data['filter_status'] != 'all' )
			$object = $object->where('status', (int) $data['filter_status'] );
		
		if ( !empty($data['filter_po_shipping']) )
			$object = $object->where('shipping_method', trim(strtoupper($data['filter_po_shipping'])) );
		
		return $object;
	}
	
	// Add List Data - Additional Data on List
	public function addListData( $data ) {
		$data['vendors'] = array('All Suppliers');
		
		$vendors = Vendor::has('purchaseOrders')->get(array('name', 'id'));
		
		foreach( $vendors as $vendor )
			$data['vendors'][$vendor->id] = ucwords($vendor->name);
	
		$data['statuses'] = array_merge( array('all' => 'All Statuses'), Constant::getPoList( true ) );
		$data['shippings'] = array_merge( array(0 => 'All Shipping Addresses'), Constant::getShippingList( true ) );

		return $data;
	}

	// Add Form Data - Additional Data on Form
	public function addFormData( $data, $id=null ) {
		if ( empty($id) ) return $data;
		
		$is_approved = ( $data['result']->status == Constant::PO_APPROVED );
		$is_manager_action = $data['isManagerAction'] = ( Auth::user()->userGroup->level == Constant::USER_MANAGER );
		$po_items = $this->data['result']->PurchaseOrderProducts()->where('archived', 0);
		$so_items = SalesOrderProduct::where( [
			'archived'	=> 0,
			'deleted'		=> 0
		] )->lists('ORDER_PRODUCT_id');
		
		$data['total_received_items'] = 0;
		$data['enable_delete']	= 0;
		foreach ( $po_items->get() as $po_item ) {
			$item_info		= $po_item->OrderProduct;
			$is_received	= (bool) $po_item->receive_include;
			$po_item_id	= (int) $po_item->id;
			$item_id		= (int) $po_item->ORDER_PRODUCT_id;

			$data['po_items'][$po_item_id] = [
				'item_id'				=> $item_id,
				'enable_delete'	=> 0,
				'order_id'			=> $item_info->ORDER_id,
				'name'				=> $item_info->name,
				'received'			=> $po_item->receive_include,
				'quantity'			=> $item_info->quantity,
				'options'				=> array(),
				'transfer_price'	=> $po_item->transfer_price
			];
			
			// Order Item Options
			foreach ( $item_info->orderProductOptions() as $op ) {
				$data['po_items'][$po_item_id]['options'][] = [
					'value'		=> $op->option_value,
					'quantity'	=> ( empty( $op->quantity ) ) ? $item_info->quantity : $op->quantity
				];
			}

			// Enable Deletion (PO Approved, Not Received, SO'ed)
			if ( $is_manager_action && $is_approved && !$is_received 
				&& in_array($po_item->ORDER_PRODUCT_id, $so_items) )
				$data['enable_delete']	= $data['po_items'][$po_item_id]['enable_delete'] = 1;
			
			if ( $is_received ) $data['total_received_items']++;
		}
		
		$data['statuses'] = Constant::getPoList( true );
		$data['order_statuses'] = Constant::getOrderList( true );
		$data['enable_update'] = $is_manager_action && 
			( $data['enable_delete'] || $this->data['result']->status == Constant::PO_PENDING );

		return $data;
	}
	
	public function preUpdate( $data ) {
		if ( isset($data['comment']) )
			$data['comment'] = trim($data['comment']);
		
		// Is currently Approved and had deleted items?
		// or Is to be P. O. to be Denied? Require Comment
 		if ( ( ($data['status'] == Constant::PO_APPROVED) && !empty($data['items']) )
 			|| ($data['status'] == Constant::PO_DENIED) ) {
 				$this->rules['comment'] = 'required';
 			}

		return $data;
	}
	
	public function postUpdate( $data, $object ) {
		$object->status = $data['status'];
		$object->comment = $data['comment'];
		$status = $object->status;

		// Has been approved? Update Order Products under it
		if ( $status == Constant::PO_APPROVED ) {
			// Is to delete PO Items?
			if ( !empty($data['items']) ) {
				// Retrieve SO Items.
				$so_items = SalesOrderProduct::where( ['archived' => 0, 'deleted' => 0] );
				$so_item_ids = $so_items->lists('ORDER_PRODUCT_id');
				
				// Retrieve PO Items. (Unarchived, Unreceived, SO'ed)
				$po_items = PurchaseOrderProduct::where('PURCHASE_ORDER_id', $object->id)
					->where( array('receive_include' => 0, 'archived' => 0) )
					->whereIn('id', $data['items'])
					->whereIn('ORDER_PRODUCT_id', $so_item_ids);
				$po_item_ids = $po_items->select('id')->lists('id');
				$item_ids = $po_items->select('ORDER_PRODUCT_id')->lists('ORDER_PRODUCT_id');

				// Has no items to update?
				if ( empty($item_ids) ) return false;

				// Archive PO Items and Move to Processing Again
				# API : UPDATE ORDER PRODUCT STATUS FROM ORDERED FROM SUPPLIER TO PROCESSING
				OrderProduct::whereIn( 'id', $item_ids )
					->where( 'archived', 0 )
					->update( ['status' => Constant::ORDER_PROCESSING] );
				PurchaseOrderProduct::where('PURCHASE_ORDER_id', $object->id)
					->whereIn( 'id', $po_item_ids )
					->update( ['archived' => 1] );
				SalesOrderProduct::whereIn( 'ORDER_PRODUCT_id', $item_ids )
					->where( 'archived', 0 )
					->update( ['deleted' => 1] );
				
				// Delete Sales Order Items when All Items are deleted
				$sales_orders = SalesOrder::whereHas('salesOrderProducts', function( $sop ) use ( $so_item_ids ) {
					$sop->whereIn('ORDER_PRODUCT_id', $so_item_ids);
					$sop->where( 'deleted', '=', 1 );
				})->where( ['archived' => 0, 'deleted' => 0] )
					->whereRaw(
						'(SELECT COUNT(*) FROM sales_order_product'
						. ' WHERE archived = 0 AND deleted = 0 AND SALES_ORDER_id = sales_order.id) = ?', array(0)
				)->update( ['deleted' => 1] );

				// Retrieve updated PO Items
				$po_items = $object->purchaseOrderProducts()->where('archived', 0);
				$new_total_transfer_price = 0;
	
				// Are all PO Items archived? Archive also PO.
 				if ( !$po_items->count() ) {
 					$object->archived = 1;
 				}
 				// Recompute Total Transfer Price
 				else {
 					foreach ( $po_items->get() as $item )
 						$new_total_transfer_price += $item->quantity * $item->transfer_price;
 				}
				
				$object->total_transfer_price = $new_total_transfer_price;
				$object->save();
				return;
			}
	
			$object->approved_by = Auth::user()->id;
			$object->approved_at = DB::raw('CURRENT_TIMESTAMP');
			$active_po_items = $object->purchaseOrderProducts()->where('archived', 0)->lists('ORDER_PRODUCT_id');
			
			# API : UPDATE ORDER PRODUCT STATUS FROM PROCESSING TO ORDERED FROM SUPPLIER
			$affected_rows = OrderProduct::whereIn( 'id', $active_po_items )
				->update( ['status' => Constant::ORDER_ORDERED_FROM_SUPPLIER] );
		}
		
		$object->save();
		
		return $data;
	}
	
	// Use to add show on Data
	public function addShowData($data, $id=null) {
		$this->data['info'] = PurchaseOrder::where('id', $id)->first();
		$this->data['po_items'] = PurchaseOrderProduct::where('PURCHASE_ORDER_id', $id)
			->where('archived', 0)
			->get();
		
		return $data;
	}
	
	/**
	 * Date & Time: 03/02/2015 - 05:26 PM
	 * @param string $id
	 * @return string
	 * Description: Added Purchase Orders View via AJAX 
	 */
	public function getPurchaseOrderdetails($id = null){
		$this->setFormData($id);
		return View::make('po-show')->with($this->data);
	}
	
	/**
	 * Approve selected POs
	 * Move selected P. O. Items to Received Status.
	 * @author Geoff 03/03/2015
	 * @return array
	 */
	public function approvePurchaseOrders() {
		$ids = explode(',', Input::get('ids') );
		
		// Has no ids to update?
		if ( empty($ids) ) return false;
		
		// Tag items as Received
		$this->_tagReceivedItems( $ids, 'po_id' );

		return [ 'success' => Lang::get('po.po_received_message') ];
	}

	/**
	 * Tag Order Items as Received with time info.
	 * @author AngelaMisa 03/20/2015 2:26PM
	 * @param $items, $type ( po_id || item_id )
	 * @return void
	 */
	private function _tagReceivedItems( $items, $type = 'item_id' ) {
		$column = ( $type == 'po_id' ) ? 'id' : 'ORDER_PRODUCT_id';
		$po_items = PurchaseOrderProduct::active()
			->whereHas('PurchaseOrder', function( $po ) {
				$po->where( 'purchase_order.archived', 0 );
				$po->where( 'purchase_order.status', Constant::PO_APPROVED );
			})
			->where( 'receive_include', 0 )
			->where( 'archived', 0 )
			->whereIn( $column, $items );
		$po_item_ids = $po_items->select('id')->lists('id');
		$item_ids = $po_items->select('ORDER_PRODUCT_id')->lists('ORDER_PRODUCT_id');
	
		// Tag selected P. O. Items as Received
		PurchaseOrderProduct::whereIn( 'id', $po_item_ids )
			->update( array(
				'receive_include'	=> 1,
				'date_received'		=> DB::raw('CURRENT_TIMESTAMP')
			));
	
		# API : UPDATE ORDER PRODUCT STATUS TO RECEIVED AND INCLUDE DATE
		// Tag order product from P. O. as received
		OrderProduct::where('archived', 0)->whereIn( 'id', $item_ids )
			->update( array(
				'status' 		=> Constant::ORDER_RECEIVED,
				'date_received'	=> DB::raw('CURRENT_TIMESTAMP')
			));
	}

	/**
	 * Export file for Purchase Order ID.
	 * @author AngelaMisa 02/27/2015
	 * @param $id, $print
	 * @return void
	 */
	public function export( $id, $template = 'default', $print = false ) {
		$po = PurchaseOrder::where('id', $id)->first();
	
		if ( empty($po) ) return App::abort(404, "Purchase Order {$id} Not Found");

		// Vendor Details
		$vendor 			= $po->vendor;
		$vendor_id 		= (int) $vendor->id;
		$vendor_code		= $vendor->code;

		// Build PO Additional Details
		$po_id = array($vendor->code, str_pad($po->id, 3, '0', STR_PAD_LEFT), date_format($po->created_at, 'Ymd'));
		$po_details = array(
			'po_number'			=> implode('_', $po_id),
			'po_date'				=> 'N/A',
			'delivery_date'			=> 'N/A',
			'prepared_by'			=> $po->createdBy->firstname . ' ' . $po->createdBy->lastname,
			'prepared_at'			=> Formatter::toDateFormat( $po->created_at, 'm/d/Y' ),
			'approved_by'			=> 'N/A',
			'approved_at'			=> 'N/A',
			'delivery_date'			=> 'N/A',
			'shipping_address'	=> array( 'address'	=> '', 'unit' => '', 'street' => '', 'city'	 => '', 'postal' => '', 'mobile' => ''),
			'comments'			=> $po->comment,
			'total_price'			=> 0,
			'total_quantity'		=> 0
		);

		// Is PO Approved already?
		if ( $po->status == Constant::PO_APPROVED ) {
			$str_approved_at = Formatter::toDateFormat( $po->approved_at, 'm/d/Y' );
			$po_details['po_date']			= $str_approved_at;
			$po_details['approved_at']	= $str_approved_at;
			$po_details['approved_by']	= $po->approvedBy->firstname . ' ' . $po->approvedBy->lastname;
			$po_details['delivery_date']	= Formatter::toDateFormat( $po->approved_at, 'm-d-Y', 7 );
		}
		
		// Retrieve Purchase Order Products
		$po_items = PurchaseOrderProduct::active()
			->join( 'purchase_order', 'purchase_order.id', '=', 'purchase_order_product.PURCHASE_ORDER_id' )
			->join( 'order_product', 'order_product.id', '=', 'purchase_order_product.ORDER_PRODUCT_id' )
			->join( 'product', 'product.id', '=', 'order_product.PRODUCT_id' )
			->join( 'product_vendor', function ( $pv ) {
				$pv->on( 'product_vendor.VENDOR_id', '=', 'purchase_order.VENDOR_id' );
				$pv->on( 'product_vendor.PRODUCT_id', '=', 'order_product.PRODUCT_id' );
			})
			->leftJoin( 'order_product_option', function ( $op ) {
				$op->on( 'order_product_option.ORDER_id', '=', 'order_product.ORDER_id' );
				$op->on( 'order_product_option.PRODUCT_id', '=', 'order_product.PRODUCT_id' );
			})
			->where( 'product_vendor.VENDOR_id', $vendor_id )
			->where( 'PURCHASE_ORDER_id', $po->id )
			->get( array(
				'order_product.id', 'order_product.ORDER_id', 'order_product.PRODUCT_id', 'order_product.quantity',
				'order_product.name', 'product.srp', 'product_vendor.product_code',
				'order_product_option.option_value', 'order_product_option.price AS option_price', 'order_product_option.quantity AS option_quantity'
			) );
		
		// Group Order Items by Product SKU
		$products = array();
		foreach ( $po_items as $po ) {
			$prod_code 	= $po->product_code;
			$prod_id 		= (int) $po->PRODUCT_id;
			$prod_qty 		= (int) $po->quantity;
			
			// Product SKU
			$prod_sku		= $vendor_code . '-' . $prod_code;
			if ( !empty($po->option_value) ) {
				$trim_sku = str_replace('-', ' ', trim($po->option_value));
				$prod_sku .= strtoupper('-' . str_replace('  ', ' ', $trim_sku));
			}
			
			// Product SKU not yet saved? Initialized
			if ( !array_key_exists($prod_sku, $products) ) {
				$products[$prod_sku] = array(
					'id'					=> $prod_id,
					'model'			=> $prod_code,
					'name'			=> $po->name,
					'sku'				=> $prod_sku,
					'quantity'		=> 0,
					'price'			=> $po->srp,
					'prod_total'		=> 0,
					'option_value'	=> $po->option_value,
					'option_price'	=> $po->option_price,
					'option_total'	=> 0,
					'option_qty'	=> 0,
					'total'				=> 0,
				);
			}
			
			// Compute for Products Total Quantity and Total Price with Additionals
			$opt_qty = ( empty($po->option_quantity) ) ? $po->quantity : $po->option_quantity;
			$prod_add_price = $po->option_price * $po->option_quantity;
			$prod_total = $prod_qty * $po->srp;
			$item_total = $prod_add_price + $prod_total;
			
			// Compute for Option Total Quantity and Additional Price
			$opt_total										= $opt_qty * $po->option_price;
			$products[$prod_sku]['quantity']		+= $prod_qty;
			$products[$prod_sku]['prod_total']	+= $prod_total;
			$products[$prod_sku]['total']			+= $item_total;
			$products[$prod_sku]['option_total'] += $opt_total;
			$products[$prod_sku]['option_qty'] 	+= $opt_qty;
			$po_details['total_price']					+= $item_total;
			$po_details['total_quantity'] 			+= $prod_qty;
			
			// Is for HOME Delivery? Get Order Product Shipping Address
			if ( $po->shipping_method == Constant::PO_SHIPPING_HOME ) {
				$order_set = OrderToSet::where(array(
					'ORDER_id'	=> $op->ORDER_id,
					'SET_id'	=> $op->SIZE_id
				))->first();
			
				// Address Found
				if ( $order_set ) {
					$po_details['shipping_address']	= array(
						'address'	=> $order_set->address,
						'unit'			=> $order_set->unit,
						'street'		=> $order_set->street . ' ' . $order_set->barangay,
						'city'			=> $order_set->city,
						'postal'		=> $order_set->zip_code,
						'mobile'		=> $order_set->mobile,
					);
				}
			} elseif ( $po->shipping_method == Constant::PO_SHIPPING_ACOMMERCE ) {
				$po_details['shipping_address']	=  array(
					'address'	=> Lang::get('info.ACOMMERCE.address'),
					'unit'			=> Lang::get('info.ACOMMERCE.building'),
					'street'		=> Lang::get('info.ACOMMERCE.street'),
					'city'			=> Lang::get('info.ACOMMERCE.city'),
					'postal'		=> Lang::get('info.ACOMMERCE.postal'),
					'mobile'		=> 'N/A',
				);
			}
		}
		
		$po = array( 'po_info' => $po_details, 'vendor'	=> $vendor, 'products' => $products, 'is_print'	=> !empty($print) );
		
		return View::make('export.po_' . $template . '_excel')->with(array_merge($po, Lang::get('css')));
	}

	/**
	 * Export List of Purchase Orders based on filters.
	 * @author AngelaMisa 03/03/2015 11:49PM
	 * @param $print
	 * @return void
	 */
	public function exportList( $print = false ) {
		$data			= Input::all();
		$po_items		= array();
		$po_options	= array();
		$po_filtered	= $this->_filterPoList( $data, PurchaseOrder::active() );
		$po_ids			=  $po_filtered->lists('id');
		
		// Has PO filtered?
		if ( $po_filtered->count() ) {
			$po_result = PurchaseOrderProduct::active()
				->whereIn('PURCHASE_ORDER_id', $po_ids );
			
			// Retrieve Purchase Order Items based on Filtered PO List
			$po_items = $po_result->join('purchase_order', 'purchase_order_product.PURCHASE_ORDER_id', '=', 'purchase_order.id')
				->join('vendor', 'purchase_order.VENDOR_id', '=', 'vendor.id')
				->join( 'order_product', 'order_product.id', '=', 'purchase_order_product.ORDER_PRODUCT_id' )
				->leftJoin( 'order_product_option', function( $op ) {
					$op->on('order_product_option.ORDER_id', '=', 'order_product.ORDER_id');
					$op->on('order_product_option.PRODUCT_id', '=', 'order_product.PRODUCT_id');
				})
				->join( 'product', 'order_product.PRODUCT_id', '=', 'product.id' )
				->join('product_vendor', function( $vendor ) {
					$vendor->on('product_vendor.PRODUCT_id', '=', 'product.id');
					$vendor->on('product_vendor.VENDOR_id', '=', 'purchase_order.VENDOR_id');
				})
				->join( 'category', 'order_product.CATEGORY_id', '=', 'category.id')
				->join( 'main_category_category', 'category.id', '=', 'main_category_category.CATEGORY_id' )
				->join( 'main_category', 'main_category_category.MAIN_CATEGORY_id', '=', 'main_category.id' )
				->join( 'user', 'user.id', '=', 'purchase_order.created_by')
				->get( array(
					'purchase_order.id AS po_id', 'purchase_order.approved_at', 'purchase_order.created_at',
					'purchase_order.company_code', 'purchase_order.status AS po_status', 'purchase_order.comment AS po_comment',
					'vendor.id AS vendor_id', 'vendor.name AS vendor_name', 'vendor.code AS vendor_code',
					'order_product.id AS item_id', 'order_product.ORDER_id', 'order_product.quantity AS quantity',
					'order_product.name AS prod_name', 'product_vendor.product_code AS prod_model', 'product.srp AS prod_srp',
					'order_product.status AS item_status', 'order_product.PRODUCT_id', 'user.lastname', 'user.firstname',
					'category.code AS category_code', 'main_category.code AS main_category_code',
					'order_product_option.price AS option_price', 'order_product_option.option_value', 'order_product_option.quantity AS option_quantity'
			));
		}
		
		$po_list['po_list'] = array(
			'cur_date'			=> Formatter::toDateFormat('now', 'Y-m-d h:i:s'),
			'is_print'				=> $print,
			'items'				=> $po_items,
			'options'				=> $po_options,
			'statuses'			=> Constant::getPoList( true ),
			'order_statuses'	=> Constant::getOrderList( true )
		);

		return View::make( 'export.po_list' )->with( array_merge($po_list, Lang::get('css')) );
	}
	
	/**
	 * Generate Sales Order Report.
	 * Item-level.
	 * @param $print
	 * @return void
	 */
	public function generateSO( $print = false ) {
		// May laman na ba si P. O. Products, kapag wala return false
		
		// Get list of ORDER_id na nasa Approved P. O.s
		
		// Incorrect
		// Dun ka na mag count kung ilan yung for S. O. (excluding XLARGE(size:large, product:supplier_delivery=1) Or NDEL
		// , where ORDER ID in ung list nakuha before.
		
		
		
		
		$count_sop=SalesOrderProduct::where('archived','=','0')->count();

		$generate_po=PurchaseOrderProduct::select('order_product.id as order_product_id','order.id as order_id')
			->join('purchase_order','purchase_order.id','=','purchase_order_product.PURCHASE_ORDER_id')
			->join('order_product','ORDER_PRODUCT_id','=','order_product.id')
			->join('order','order_product.ORDER_id','=','order.id');
		
		if( $count_sop ){
			$generate_po=$generate_po->join('sales_order_product','order_product.id','=','sales_order_product.ORDER_PRODUCT_id');
			$generate_po=$generate_po->where('sales_order_product.ORDER_PRODUCT_id','<>','order_product.id');
		}
		
		$generate_po=$generate_po->where('purchase_order.status','=',1);	
		$generate_po=$generate_po->whereNotIn('order_product.SIZE_id',array(4,7));
		$generate_po=$generate_po->whereRaw('(SELECT COUNT(*) FROM order_product  WHERE id=purchase_order_product.ORDER_PRODUCT_id AND status IN (2,3))
				=(SELECT COUNT(*) FROM order_product WHERE id=purchase_order_product.ORDER_PRODUCT_id)')->count();

		
		/*WHERE purchase_order.`status`=1
		AND  order_product.`SIZE_id`NOT IN (4,7)
		AND ((SELECT COUNT(*) FROM order_product op WHERE op.id=purchase_order_product.ORDER_PRODUCT_id AND `status` IN (2,3) == (SELECT COUNT(*) FROM order_product WHERE op.id=purchase_order_product.ORDER_PRODUCT_id))
		exit;*/
		$get_data = PurchaseOrderProduct::select( array(
			'order.id','order.payment_method', 'order.created_at', 'order_product.quantity', 'order_product.benstore_price',
			'login.email', 'login.firstname', 'login.lastname', 'order_product.peso', 'order_product.SIZE_id',
			'order_to_set.unit', 'order_to_set.street', 'order_to_set.barangay', 
			'order_to_set.city', 'order_to_set.zip_code', 'order_to_set.mobile',
			'product.code' ) )
		->join( 'purchase_order', 'purchase_order_product.PURCHASE_ORDER_id','=','purchase_order.id' )
		->join( 'order_product','order_product.id','=','purchase_order_product.ORDER_PRODUCT_id' )
		->join( 'order' , 'order_product.ORDER_id', '=', 'order.id' )
		->leftJoin( 'order_to_set', function ( $order_set ) {
			$order_set->on('order_to_set.ORDER_id', '=', 'order_product.ORDER_id')
				->on('order_to_set.SET_id', '=', 'order_product.SIZE_id');
		})
		->join( 'login', 'order.EMPLOYEE_id', ' = ', 'login.id')
		->join( 'product', 'order_product.PRODUCT_id', ' = ', 'product.id')
		->where( 'purchase_order.status', Constant::PO_APPROVED )
		->where( 'order.so_batch','=', null )
		->where( 'order_product.status', '=' , Constant::ORDER_ORDERED_FROM_SUPPLIER )//3
		//->where( 'order.status', '=' , Constant::ORDER_ORDERED_FROM_SUPPLIER )
		->whereRaw( '(SELECT COUNT(*) FROM order_product op WHERE op.ORDER_id = order_product.ORDER_id and SIZE_id <> 7)
			=(SELECT COUNT(*) FROM order_product op WHERE op.ORDER_id = order_product.ORDER_id AND op.status=2)' );
		$get_so['so_list'] = $get_data->get();
		$get_so['so_statuses'] = Constant::getOrderList( true );
		$get_so['is_print'] = (bool) $print;
		
		return View::make( 'export.salesorder' )->with( $get_so );
	}
	
	/**
	 * View of Upload approved PO statuses
	 * @author Geoffrey
	 * @return view
	 */
	public function uploadPurchaseOrderStatus(){
		return View::make('po-upload_purchase_order_status');
	}
	
	/**
	 * Post upload file of PO Status.
	 * Tag P. O. Items as Received.
	 * @author Geoffrey
	 * @return array
	 */
	public function postUploadPurchaseOrderStatus() {
		// Get and Move file to Assets/Uploads Directory
		$dir 		= Config::get('app.lbea');
		$file		= Input::file('postUploadPurchaseOrderStatus');
		$moved	= $file->move( $dir, $file->getClientOriginalName() );

		// Has successfully added file in Uploads? Read file.
		$uploaded_file	= array();
		if ( $moved )
			$uploaded_file = fopen($dir . '/' . Input::file('postUploadPurchaseOrderStatus')->getClientOriginalName(), 'r');
		
		// Has read uploaded file?
		$uploaded_data	= array();
		$item_ids = array();
		if( $uploaded_file ) {
			$c = 0;
			$row = 0;

			// Append cell values per row to Array Data
			while ( ($data = fgetcsv($uploaded_file, 1000, ",")) !== FALSE  ) {
				$num = count($data);
				$row++;

				for ( $c = 0; $c < $num; $c++ ) {
					if ( $c == 0 && $row > 1 ) $item_ids[] = $data[0];
					$uploaded_data[$row][] = $data[$c];
				}
			}

			fclose($uploaded_file);
		}

		// Remove CSV Headers
		unset($uploaded_data[1]);
		
		// Tag items in Order Product and Purchase Order Product as Received
		if ( !empty($item_ids) ) 
			$this->_tagReceivedItems( $item_ids );

		return [ 'success' => 1 ];
	}

	/**
	 * Retrieve preview list.
	 * @author AngelaMisa 03/12/2015
	 * @param $level, $status, $limit
	 * @return mixed
	 */
	public function getPreviewList( $level, $status = 0, $limit = 5 ) {
		$data['level']	= $level;
		$data['po_statuses'] = Constant::getPoList(true);
		$status = explode(',', $status);

		// Item Level
		if ( $level == 'item' ) {
			$data['results'] = PurchaseOrderProduct::active()
				->whereHas('PurchaseOrder', function( $po ) use ( $status ) {
					$po->where('status', $status[0]);
				})
				->where('receive_include', 0)
				->limit( $limit )->get();
		} else {
			foreach ( $status as $s ) {
				$result = PurchaseOrder::active()
					->where('status', $s)
					->limit( $limit )->get();

				$data['results'][$s] = $result;
			}
		}
	
		return View::make( 'po-preview-list' )->with( $data );
	}
	
public function sendVoucher($id){
		$columns = [
			'purchase_order_product.id as po_item_id',
			'order_product.id as order_item_id',
			'order_product.PRODUCT_id as PRODUCT_id',
			'order_product.ORDER_id as order_id',
			'purchase_order_product.PURCHASE_ORDER_id as purchase_order_id',
			'purchase_order_product.created_at'
		];
		
		$outputName = str_random(6);
		$filename = 'voucher-detail_' . $outputName . '.pdf';
		$pdfPath = Config::get('app.temp') . $filename;
		
		$pdf_data = [];
		if(PurchaseOrderProductVoucher::where('PURCHASE_ORDER_PRODUCT_id', '=', Input::get('po_item_id'))->count() > 0){
			$pdf_data['po_item_voucher'] = PurchaseOrderProductVoucher::where('PURCHASE_ORDER_PRODUCT_id', '=', Input::get('po_item_id'))->first();
		} else {
			$po_item = PurchaseOrderProduct::join('order_product', 'order_product.id', '=', 'purchase_order_product.ORDER_PRODUCT_id')
			->where('purchase_order_product.id', '=', Input::get('po_item_id'))->get($columns)->first();
			
			$voucher_owner = PurchaseOrderProduct::join('order_product', 'order_product.id', '=', 'purchase_order_product.ORDER_PRODUCT_id')
			->join('order', 'order.id', '=', 'order_product.ORDER_id')
			->join('login', 'login.id', '=', 'order.EMPLOYEE_id')
			->where('purchase_order_product.id', '=', Input::get('po_item_id'))->get(['login.firstname', 'login.lastname'])->first();
			
			$vendor_code = Vendor::where('id', '=', PurchaseOrder::where('id', '=', $po_item->purchase_order_id)->get()->first()->VENDOR_id)->get()->first()->code;
			
			$insert_po_item_voucher = [
				'PURCHASE_ORDER_PRODUCT_id' => $po_item->po_item_id,
				'product_name'				=> Product::where('id', '=', $po_item->PRODUCT_id)->first()->name,
				'voucher_description'		=> Product::where('id', '=', $po_item->PRODUCT_id)->first()->voucher_description,
				'supplier_name'				=> Product::where('product.id', '=', $po_item->PRODUCT_id)->join('product_vendor', 'product_vendor.PRODUCT_id', '=', 'product.id')->join('vendor', 'vendor.id', '=', 'product_vendor.VENDOR_id')->get(['vendor.name'])->first()->name,
				'voucher_owner'				=> $voucher_owner->firstname . ' ' . $voucher_owner->lastname,
				'voucher_code'				=> strtoupper($vendor_code) . '-' . str_pad($po_item->purchase_order_id, 3, '0', STR_PAD_LEFT) . '-' . date_format($po_item->created_at, 'Ymd') . '-' . str_pad($po_item->po_item_id, 3, '0', STR_PAD_LEFT),
				'date_issued'				=> date('Y-m-d h:i:s'),
				'redeem_address'			=> '' // OrderToSet::where('ORDER_id', '=', $po_item->order_id)->get(['address'])->first()->address
			];
			
			$po_item_voucher = new PurchaseOrderProductVoucher();
			
			$po_item_voucher->PURCHASE_ORDER_PRODUCT_id = $insert_po_item_voucher['PURCHASE_ORDER_PRODUCT_id'];
			$po_item_voucher->product_name				= $insert_po_item_voucher['product_name'];
			$po_item_voucher->voucher_description		= $insert_po_item_voucher['voucher_description'];
			$po_item_voucher->supplier_name				= $insert_po_item_voucher['supplier_name'];
			$po_item_voucher->voucher_owner				= $insert_po_item_voucher['voucher_owner'];
			$po_item_voucher->voucher_code				= $insert_po_item_voucher['voucher_code'];
			$po_item_voucher->redeem_address			= $insert_po_item_voucher['redeem_address'];
			$po_item_voucher->date_issued				= $insert_po_item_voucher['date_issued'];
			$po_item_voucher->archived					= 0;
			
			$po_item_voucher->save();
			
			$data['po_item_voucher'] = PurchaseOrderProductVoucher::where('PURCHASE_ORDER_PRODUCT_id', '=', Input::get('po_item_id'))->first();
			$pdf_data['po_item_voucher'] = PurchaseOrderProductVoucher::where('PURCHASE_ORDER_PRODUCT_id', '=', Input::get('po_item_id'))->first();
		}
		
		$html = View::make('export/product_voucher')->with($pdf_data);
		File::put($pdfPath, PDF::load($html, 'A4', 'portrait')->output());
		
		$s3 = App::make('aws')->get('s3');
		
		$s3->putObject([
				'Bucket'     => 'sb-public-rep',
				'Key'        => 'docs/voucher-details/'.$filename,
				'SourceFile' => Config::get('app.temp').$filename,
				'ACL'        => 'public-read',
		]);
		
		PurchaseOrderProductVoucher::where('PURCHASE_ORDER_PRODUCT_id', '=', Input::get('po_item_id'))
		->update([
			'assigned_token' => md5(Input::get('po_item_id')),
			'received' => Constant::VOUCHER_SENT
		]);
		
		$data = [];
		$data['pdf_link_download'] =  Asset::cdn("docs/voucher-details/" . $filename);
		
		$link_query = [
			'download' => 'true',
			'file_link' => Crypt::encrypt($data['pdf_link_download']),
			'po_item_id' => Input::get('po_item_id'),
			'token' => md5(Input::get('po_item_id'))
		];
		
		$data['url'] = URL::to('po/getReceivingVoucher/' . $id) . '?' . http_build_query($link_query);
		$data['po_item_voucher'] = PurchaseOrderProductVoucher::where('PURCHASE_ORDER_PRODUCT_id', '=', Input::get('po_item_id'))->first();
		
		Mail::send('emails.po_item_voucher', $data, function($message) {
			$message->to('geoffrey-compra-embuscado@live.com', 'Demo')->subject(Lang::get('po.text_mail_subj'));
		});
		
	}
	
	/**
	 * Voucher details received.
	 * @author Geoff 03/26/2015 - 01:35PM
	 * @param none
	 */
	public function getReceivingVoucher($id){
		$data = [];
		$data['po_item_id'] = Input::get('po_item_id');
		$data['url'] = Crypt::decrypt(Input::get('file_link'));
		$po_item_voucher = PurchaseOrderProductVoucher::where('PURCHASE_ORDER_PRODUCT_id', '=', Input::get('po_item_id'))->first();
		
		OrderProduct::where('id', '=', PurchaseOrderProduct::where('id', '=', $data['po_item_id'])->get()->first()->ORDER_PRODUCT_id)
			->update([
				'status' => Constant::ORDER_RECEIVED
			]);
		
		PurchaseOrderProduct::where('id', '=', $data['po_item_id'])
			->update([
				'receive_include' => 1
			]);
		
		if($po_item_voucher->assigned_token == Input::get('token')){
			PurchaseOrderProductVoucher::where('PURCHASE_ORDER_PRODUCT_id', '=', Input::get('po_item_id'))
			->where('assigned_token', '=', md5(Input::get('po_item_id')))
			->update([
				'assigned_token' => '',
				'received' => Constant::VOUCHER_RECEIVED
			]);
		}
		return Redirect::to($data['url']);
	}
	
	/**
	 * Generate Voucher template of product
	 * @author Geoff
	 * @param @id
	 */
	public function generateVoucher($id){
		$columns = [
			'purchase_order_product.id as po_item_id',
			'order_product.id as order_item_id',
			'order_product.PRODUCT_id as PRODUCT_id',
			'order_product.ORDER_id as order_id',
			'purchase_order_product.PURCHASE_ORDER_id as purchase_order_id',
			'purchase_order_product.created_at'
		];
	
		$po_item_id = Input::get('po_item_id');
		$is_download = Input::get('download') ? true : false;
		
		$po_item = PurchaseOrderProduct::join('order_product', 'order_product.id', '=', 'purchase_order_product.ORDER_PRODUCT_id')
		->where('purchase_order_product.id', '=', $po_item_id)->get($columns)->first();
		
		$data = [];
		
		if(PurchaseOrderProductVoucher::where('PURCHASE_ORDER_PRODUCT_id', '=', $po_item_id)->count() > 0){
			$data['po_item_voucher'] = PurchaseOrderProductVoucher::where('PURCHASE_ORDER_PRODUCT_id', '=', $po_item_id)->first();
		} else {
			$po_item = PurchaseOrderProduct::join('order_product', 'order_product.id', '=', 'purchase_order_product.ORDER_PRODUCT_id')
			->where('purchase_order_product.id', '=', $po_item_id)->get($columns)->first();
			
			$voucher_owner = PurchaseOrderProduct::join('order_product', 'order_product.id', '=', 'purchase_order_product.ORDER_PRODUCT_id')
			->join('order', 'order.id', '=', 'order_product.ORDER_id')
			->join('login', 'login.id', '=', 'order.EMPLOYEE_id')
			->where('purchase_order_product.id', '=', $po_item_id)->get(['login.firstname', 'login.lastname'])->first();
			
			$vendor_code = Vendor::where('id', '=', PurchaseOrder::where('id', '=', $po_item->purchase_order_id)->get()->first()->VENDOR_id)->get()->first()->code;
			$redeem_address = PurchaseOrderProduct::join('order_product', 'order_product.id', '=', 'purchase_order_product.ORDER_PRODUCT_id')
				->join('order', 'order.id', '=', 'order_product.ORDER_id')
				->leftJoin('order_product_option', function($join){
					$join->on('order_product_option.ORDER_id', '=', 'order.id');
					$join->on('order_product_option.PRODUCT_id', '=', 'order_product.PRODUCT_id');
				})
				->leftJoin('product', 'product.id', '=', 'order_product.PRODUCT_id')
				->leftJoin('product_option', 'product_option.PRODUCT_id', '=', 'product.id')
				->whereRaw('purchase_order_product.id = ? AND LCASE(product_option.title) LIKE \'%branch%\' ', [$po_item_id])
				->get(['order_product_option.option_value AS redeem_at'])->first();Backtrack::debug($redeem_address);exit;
				
			$insert_po_item_voucher = [
				'PURCHASE_ORDER_PRODUCT_id' => $po_item->po_item_id,
				'product_name'				=> Product::where('id', '=', $po_item->PRODUCT_id)->first()->name,
				'voucher_description'		=> Product::where('id', '=', $po_item->PRODUCT_id)->first()->voucher_description,
				'supplier_name'				=> Product::where('product.id', '=', $po_item->PRODUCT_id)->join('product_vendor', 'product_vendor.PRODUCT_id', '=', 'product.id')->join('vendor', 'vendor.id', '=', 'product_vendor.VENDOR_id')->get(['vendor.name'])->first()->name,
				'voucher_owner'				=> $voucher_owner->firstname . ' ' . $voucher_owner->lastname,
				'voucher_code'				=> strtoupper($vendor_code) . '-' . str_pad($po_item->purchase_order_id, 3, '0', STR_PAD_LEFT) . '-' . date_format($po_item->created_at, 'Ymd') . '-' . str_pad($po_item->po_item_id, 3, '0', STR_PAD_LEFT),
				'date_issued'				=> date('Y-m-d h:i:s'),
				'redeem_address'			=> ($redeem_address->redeem_at) ? $redeem_address->redeem_at : '' // OrderToSet::where('ORDER_id', '=', $po_item->order_id)->get(['address'])->first()->address
			];
			
			$po_item_voucher = new PurchaseOrderProductVoucher();
			
			$po_item_voucher->PURCHASE_ORDER_PRODUCT_id = $insert_po_item_voucher['PURCHASE_ORDER_PRODUCT_id'];
			$po_item_voucher->product_name				= $insert_po_item_voucher['product_name'];
			$po_item_voucher->voucher_description		= $insert_po_item_voucher['voucher_description'];
			$po_item_voucher->supplier_name				= $insert_po_item_voucher['supplier_name'];
			$po_item_voucher->voucher_owner				= $insert_po_item_voucher['voucher_owner'];
			$po_item_voucher->voucher_code				= $insert_po_item_voucher['voucher_code'];
			$po_item_voucher->redeem_address			= $insert_po_item_voucher['redeem_address'];
			$po_item_voucher->date_issued				= $insert_po_item_voucher['date_issued'];
			$po_item_voucher->archived					= 0;
			
			$po_item_voucher->save();
			
			$data['po_item_voucher'] = PurchaseOrderProductVoucher::where('PURCHASE_ORDER_PRODUCT_id', '=', $po_item_id)->first();
		}
		
		$html = View::make('export/product_voucher')->with($data);
		
		$headers = array('Content-Type' => 'application/pdf');
		
		if($is_download == true){
			PurchaseOrderProductVoucher::where('PURCHASE_ORDER_PRODUCT_id', '=', Input::get('po_item_id'))->update([ 'received' => 1 ]);
			
			$html = View::make('export/product_voucher')->with($data);
		
			$headers = array('Content-Type' => 'application/pdf');
			
			return Response::make(PDF::load($html, 'A4', 'portrait')->show('my_pdf')->download(), 200, $headers);
		}
		
		// return PDF::load($html, 'A4', 'portrait')->show()->download();
		return Response::make(PDF::load($html, 'A4', 'portrait')->show('my_pdf'), 200, $headers);
	}
	
	/**
	 * Edit Voucher of Purchase Order Product
	 * @author Geoff - 03/19/2015 14:10PM
	 * @param none
	 */
	public function editVoucherForm(){
		$columns = [
			'purchase_order_product.id as po_item_id',
			'order_product.id as order_item_id',
			'order_product.PRODUCT_id as PRODUCT_id',
			'order_product.ORDER_id as order_id',
			'purchase_order_product.PURCHASE_ORDER_id as purchase_order_id',
			'purchase_order_product.created_at'
		];
		
		$po_item_id = Input::get('po_item_id');
		
		$po_item = PurchaseOrderProduct::join('order_product', 'order_product.id', '=', 'purchase_order_product.ORDER_PRODUCT_id')
		->where('purchase_order_product.id', '=', $po_item_id)->get($columns)->first();
		
		$voucher_owner = PurchaseOrderProduct::join('order_product', 'order_product.id', '=', 'purchase_order_product.ORDER_PRODUCT_id')
			->join('order', 'order.id', '=', 'order_product.ORDER_id')
			->join('login', 'login.id', '=', 'order.EMPLOYEE_id')
			->where('purchase_order_product.id', '=', $po_item_id)->get(['login.firstname', 'login.lastname'])->first();
		
		$vendor_code = Vendor::where('id', '=', PurchaseOrder::where('id', '=', $po_item->purchase_order_id)->get()->first()->VENDOR_id)->get()->first()->code;
		
		$insert_po_item_voucher = [
			'PURCHASE_ORDER_PRODUCT_id' => $po_item->po_item_id,
			'product_name'				=> Product::where('id', '=', $po_item->PRODUCT_id)->first()->name,
			'voucher_description'		=> Product::where('id', '=', $po_item->PRODUCT_id)->first()->voucher_description,
			'supplier_name'				=> Product::where('product.id', '=', $po_item->PRODUCT_id)->join('product_vendor', 'product_vendor.PRODUCT_id', '=', 'product.id')->join('vendor', 'vendor.id', '=', 'product_vendor.VENDOR_id')->get(['vendor.name'])->first()->name,
			'voucher_owner'				=> $voucher_owner->firstname . ' ' . $voucher_owner->lastname,
			'voucher_code'				=> strtoupper($vendor_code) . '-' . str_pad($po_item->purchase_order_id, 3, '0', STR_PAD_LEFT) . '-' . date_format($po_item->created_at, 'Ymd') . '-' . str_pad($po_item->po_item_id, 3, '0', STR_PAD_LEFT),
			'redeem_address'			=> '', // OrderToSet::where('ORDER_id', '=', $po_item->order_id)->get(['address'])->first()->address,
			'date_issued'				=> date('Y-m-d h:i:s')
		];
		
		if(PurchaseOrderProductVoucher::where('PURCHASE_ORDER_PRODUCT_id', '=', $po_item_id)->count() == 0){
			
			$po_item_voucher = new PurchaseOrderProductVoucher;
				
			$po_item_voucher->PURCHASE_ORDER_PRODUCT_id = $insert_po_item_voucher['PURCHASE_ORDER_PRODUCT_id'];
			$po_item_voucher->product_name				= $insert_po_item_voucher['product_name'];
			$po_item_voucher->voucher_description		= $insert_po_item_voucher['voucher_description'];
			$po_item_voucher->supplier_name				= $insert_po_item_voucher['supplier_name'];
			$po_item_voucher->voucher_owner				= $insert_po_item_voucher['voucher_owner'];
			$po_item_voucher->voucher_code				= $insert_po_item_voucher['voucher_code'];
			$po_item_voucher->redeem_address			= $insert_po_item_voucher['redeem_address'];
			$po_item_voucher->date_issued				= $insert_po_item_voucher['date_issued'];
			$po_item_voucher->archived					= 0;
			
			$po_item_voucher->save();
		}
		
		return View::make('po-edit-voucher')->with([ 'po_item_voucher' => PurchaseOrderProductVoucher::where('PURCHASE_ORDER_PRODUCT_id', '=', $po_item_id)->first() ]);
	}
	
	/**
	 * Update Voucher of Purchase Order Product
	 * @author Geoff - 03192015
	 * @param none
	 */
	public function updateVoucherDetails(){
		$input = Input::all();
		
		PurchaseOrderProductVoucher::where('PURCHASE_ORDER_PRODUCT_id', '=', $input['PURCHASE_ORDER_PRODUCT_id'])
		->update([
			'product_name'			=> $input['product_name'],
			'supplier_name'			=> $input['supplier_name'],
			'voucher_owner'			=> $input['voucher_owner'],
			'voucher_code'			=> $input['voucher_code'],
			'date_issued'			=> date_format(date_create($input['date_issued']), "Y-m-d h:i:s"),
			'voucher_description'	=> $input['voucher_description']
		]);
		
		return json_encode([ 'success' => 1 ]);
	}

	/**
	 * Generate Stickers for PO Items.
	 * Note: If no P. O. ids indicated, All Approved P.O. sticker will be generated.
	 * @author AngelaMisa 03/24/2015 9:00PM
	 * @param $ids
	 * @return void
	 */
	public function generateStickers( $ids, $download = 0) {
		$ids = explode(',', $ids );
	
		if ( empty($ids) ) return false;
	
		$items = PurchaseOrderProduct::active()
			->join('purchase_order', 'purchase_order.id', '=', 'purchase_order_product.PURCHASE_ORDER_id')
			->join('order_product', 'order_product.id', '=', 'purchase_order_product.ORDER_PRODUCT_id')
			->join('order', 'order_product.ORDER_id', '=', 'order.id')
			->join('login', 'login.id', '=', 'order.EMPLOYEE_id')
			->join('company', 'company.id', '=', 'order.COMPANY_id')
			->join('company_branch', 'company_branch.id', '=', 'login.BRANCH_id')
			->join('vendor', 'vendor.id', '=', 'purchase_order.VENDOR_id')
			->leftJoin('order_product_option', function( $op ) {
				$op->on( 'order_product_option.ORDER_id', '=', 'order_product.ORDER_id' );
				$op->on( 'order_product_option.PRODUCT_id', '=', 'order_product.PRODUCT_id' );
				$op->on( 'order_product_option.ORDER_PRODUCT_OPTION_id', '=', 'order_product.ORDER_PRODUCT_OPTION_id' );
			})
			->where( array(
					'purchase_order.archived'	=> 0,
					'purchase_order.status'		=> Constant::PO_APPROVED
			))
			->whereIn('purchase_order.id', $ids)
			->get( array(
					'purchase_order.id AS po_id', 'purchase_order.shipping_method',
					DB::raw("CONCAT(vendor.code, '-', LPAD(purchase_order.id, 3, '0'), '-', DATE_FORMAT(purchase_order.created_at,'%Y%m%d')) AS po_number"),
					'purchase_order_product.ORDER_PRODUCT_id', 'order_product.name AS product_name', 'order_product.quantity',
					'order_product_option.option_value', 'order_product_option.quantity AS option_quantity',
					'purchase_order.company_code', 'login.BRANCH_id', 'login.firstname', 'login.lastname'
			));
		
		$html = View::make('export/po_stickers')->with( array('items' => $items) );
		$name = 'PO Stickers ' . strtotime('now');
		$pdf = PDF::load($html, 'A4', 'portrait');
		$pdf = !empty($download) ? $pdf->download($name) : $pdf->show($name);

		return Response::make($pdf, 200, array('Content-Type' => 'application/pdf'));
	}
}